package util;

import java.net.InetAddress;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: jmc
 * @Date: 17/7/4
 */
public class JdbcUtil {

    private static Connection conn = null;
    private static Connection connReport = null;

    static {
        try {
            JschManager.init();
            InetAddress localhost = InetAddress.getLocalHost();
//            Class.forName("com.mysql.jdbc.Driver");
            //测试环境数据库
            conn = DriverManager.getConnection("jdbc:mysql://"+localhost.getHostAddress()+":3307", "iss_dev", "dev4iss");
            //测试统计数据库
            connReport = DriverManager.getConnection("jdbc:mysql://"+localhost.getHostAddress()+":3309", "iss_rw", "123456");
        } catch (Exception e) {
        }
    }

    public static String sqlString(String sql){
        String result = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
             ps = conn.prepareStatement(sql);
             rs = ps.executeQuery();
            while (rs.next()){
                result = rs.getString(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            mysqlSelectClose(ps,rs);
        }
        return result;
    }


    public static int sqlInt(String sql){
        int result = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
             ps = conn.prepareStatement(sql);
             rs = ps.executeQuery();
            while (rs.next()){
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            mysqlSelectClose(ps,rs);
        }
        return result;
    }

    public static boolean sqlExecute(String sql){
        boolean result =false;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            result = ps.execute(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            mysqlSelectClose(ps,null);
        }
        return result;


    }

    public static boolean sqlReportExecute(String sql){
        boolean result =false;
        PreparedStatement ps = null;
        try {
            ps = connReport.prepareStatement(sql);
            result = ps.execute(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            mysqlSelectClose(ps,null);
        }
        return result;


    }

    public static String sqlStringReportExecute(String sql){
        String result = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connReport.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                result = rs.getString(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            mysqlSelectClose(ps,rs);
        }
        return result;
    }



    public static List<Integer> sqlIntList(String sql){
        List<Integer> list = new ArrayList<Integer>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
             ps = conn.prepareStatement(sql);//创建了一个sql的对象
             rs = ps.executeQuery();
            while (rs.next()){//遍历行
                list.add(rs.getInt(1));//取列值
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            mysqlSelectClose(ps,rs);
        }
        return list;
    }

    private static void mysqlSelectClose(PreparedStatement ps ,ResultSet rs ){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    public static void main(String[] args) throws SQLException {
        String sql = "SELECT visit_name FROM itest.u_auto_result WHERE jenkins_name ='test01' AND jenkins_id = 284";
        JdbcUtil.sqlReportExecute(sql);
//        String abc = JdbcUtil.sqlStringReportExecute(sql);
//        System.out.println(abc);
    }

}
